
  PROCEDURE JT_HB_OFFER_LIST IS
    ERRCODE               NUMBER;
    ERRMSG                VARCHAR2(200);
    ACT_MSG               NUMBER(2);
    TABLE_NAME            VARCHAR2(30);
    V_SQL                 VARCHAR2(1000);
    V_OFR_ID              NUMBER(14);
    V_OFFER_ID            VARCHAR2(30);
    V_OFFER_TYPE          NUMBER(1);
    V_ACCT_LEVEL          NUMBER(1);
    V_RETURN_TYPE         NUMBER(1);
    V_OBJECT_LEVEL        NUMBER(1);
    V_SHOULD_SUBSIDY      NUMBER(8);
    V_MONTH_SUBSIDY       NUMBER(8);
    V_EFF_DATE            DATE;
    V_EXP_DATE            DATE;
    V_SUBSIDY_OBJECT      VARCHAR2(2);
    V_AS_OWN_FEE          NUMBER(1);
    V_CAN_DRAW            NUMBER(1);
    V_CAN_CARRY           NUMBER(1);
    V_FIRST_MONTH_SUBSIDY NUMBER;
    V_RETURN_RULE         VARCHAR2(100);
    V_START_TIME          DATE;
    V_END_TIME            DATE;
    V_BATCH_LOG_INTERVAL  NUMBER;

    CURSOR OFR_CURSOR IS
      SELECT OFR_ID,
             OFR_CODE,
             DECODE(PROM_APP_TYPE,
                    '订购业务送费',
                    3,
                    '购机送费',
                    2,
                    '存费送费',
                    1,
                    1),
             SUBSTR(OFR_NAME, 1, 30),
             EFF_DATE,
             EXP_DATE
        FROM HSS.TB_PRD_OFR@HSS C
       WHERE C.PROM_APP_TYPE IN ('订购业务送费', '购机送费', '存费送费')
         AND NOT EXISTS
       (SELECT * FROM JT_HB_OFFER_DEF WHERE OFR_ID = C.OFR_ID)
         AND ofr_code NOT LIKE 'SY%'
         and ofr_code NOT LIKE 'MF%';

  BEGIN

    EXECUTE IMMEDIATE '
   select print_log_seq.NEXTVAL@ics  from dual'
      INTO V_BATCH_LOG_INTERVAL;

    V_START_TIME := SYSDATE;

    INSERT INTO BATCH_LOG@ICS
      (SEQ, START_TIME, STATUS, IN_MONTH, PROGRAM_NAME)
    VALUES
      (V_BATCH_LOG_INTERVAL,
       V_START_TIME,
       0,
       TO_CHAR(V_START_TIME, 'yyyymm'),
       'JT_HB_COUNT.JT_HB_OFFER_LIST');
    COMMIT;

    UPDATE JT_HB_OFFER_DEF SET P_NUM = 0 WHERE 1 = 1;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('222');
    IF OFR_CURSOR%ISOPEN THEN
      CLOSE OFR_CURSOR;
    END IF;

    OPEN OFR_CURSOR;
    LOOP

      FETCH OFR_CURSOR
        INTO V_OFR_ID,
             V_OFFER_ID,
             V_OFFER_TYPE,
             V_RETURN_RULE,
             V_EFF_DATE,
             V_EXP_DATE;
      EXIT WHEN OFR_CURSOR%NOTFOUND;

      --DBMS_OUTPUT.PUT_LINE('111');
      --适用范围：帐目级
      V_ACCT_LEVEL := 1;
      --返还类型：定额
      V_RETURN_TYPE := 1;

      --优惠级别 1:帐户级 2：设备级
      SELECT DECODE(MAX(OFR_DETAIL_TYPE_ID),
                    'A1',
                    2,
                    'B1',
                    2,
                    'F3',
                    2,
                    'F4',
                    2,
                    'F5',
                    2,
                    'E1',
                    1,
                    'K1',
                    1,
                    2)
        INTO V_OBJECT_LEVEL
        FROM HSS.TB_PRD_OFR_DETAIL@HSS
       WHERE OFR_ID = V_OFR_ID;


      SELECT nvl(MAX(order_cst * 100), 0)
        INTO V_SHOULD_SUBSIDY
        FROM TB_INT_CRM_PROMOTION_MODE@hss
       WHERE X_PROMOTION_NUM = V_OFFER_ID;

      -- 每月返还额
      SELECT ceil(nvl(MAX(order_cst * 100 / attrib_42), 0))
        INTO V_MONTH_SUBSIDY
        FROM TB_INT_CRM_PROMOTION_MODE@hss
       WHERE X_PROMOTION_NUM = V_OFFER_ID
         AND attrib_42 <> 0
         AND attrib_42 IS NOT NULL
         AND attrib_43 = '月';

      --  DBMS_OUTPUT.PUT_LINE(V_SHOULD_SUBSIDY||'--'V_MONTH_SUBSIDY );
      --订购规则：指定套餐
      V_SUBSIDY_OBJECT := '02';
      --是否可抵扣欠费 0：否，1：是
      V_AS_OWN_FEE := 0;
      --是否可提取 0：否
      V_CAN_DRAW := 0;
      --是否可结转
      V_CAN_CARRY := 0;

      --首月返还额
      SELECT NVL(SUM(CALC_FORMULA), 0)
        INTO V_FIRST_MONTH_SUBSIDY
        FROM (SELECT DISTINCT OFR_ID, OFR_DETAIL_TYPE_ID, CALC_FORMULA
                FROM (SELECT A.OFR_ID,
                             A.OFR_DETAIL_REF_ID,
                             DECODE(A.OFR_DETAIL_TYPE_ID,
                                    'A1',
                                    '设备级',
                                    'B1',
                                    '设备级',
                                    'F3',
                                    '设备级',
                                    'F4',
                                    '设备级',
                                    'F5',
                                    '设备级',
                                    'E1',
                                    '账户级',
                                    'K1',
                                    '账户级') OFR_DETAIL_TYPE_ID,
                             B.EVENT_PRICING_STRATEGY_ID,
                             C.PRICING_SECTION_ID,
                             F.LEFT_FORMULA,
                             F.COMPARE_OPERATOR,
                             F.RIGHT_FORMULA,
                             D.DISCOUNT_EXPRESS_ID,
                             D.CALC_FORMULA,
                             E.OBJECT_SOURCE_ID
                        FROM HSS.TB_PRD_OFR_DETAIL@HSS         A,
                             HSS.V_BIL_MAND_STRATEGY_21@HSS    B,
                             HSS.V_BIL_PRICING_SECTION@HSS     C,
                             HSS.V_BIL_DISCOUNT_EXPRESS@HSS    D,
                             HSS.V_BIL_REF_CODE@HSS            E,
                             HSS.TB_BIL_DISCOUNT_CONDITION@HSS F
                       WHERE A.OFR_ID = B.PRICING_PLAN_ID
                         AND B.EVENT_PRICING_STRATEGY_ID =
                             C.EVENT_PRICING_STRATEGY_ID
                         AND C.PRICING_SECTION_ID = D.PRICING_SECTION_ID
                         AND B.EVENT_PRICING_STRATEGY_ID =
                             E.EVENT_PRICING_STRATEGY_ID
                         AND A.OFR_ID = E.OFR_ID
                         AND A.OFR_DETAIL_ID = E.OBJECT_SOURCE_ID
                         AND F.CONDITION_ID = C.CONDITION_ID
                         AND F.LEFT_FORMULA NOT LIKE 'RO_DT_TM%'
                         AND REGEXP_LIKE(CALC_FORMULA, '^[-][1-9][0-9]*$')
                         AND A.OFR_ID = V_OFR_ID));
      --  DBMS_OUTPUT.PUT_LINE('444');
      --插入到销售品规格数据表
      IF V_SHOULD_SUBSIDY > 0 THEN
        IF V_MONTH_SUBSIDY > 0 THEN
          INSERT INTO JT_HB_OFFER_DEF
            (OFR_ID,
             OFFER_ID,
             OFFER_TYPE,
             ACCT_LEVEL,
             RETURN_TYPE,
             OBJECT_LEVEL,
             SHOULD_SUBSIDY,
             MONTH_SUBSIDY,
             EFF_DATE,
             EXP_DATE,
             SUBSIDY_OBJECT,
             AS_OWN_FEE,
             CAN_DRAW,
             CAN_CARRY,
             FIRST_MONTH_SUBSIDY,
             RETURN_RULE,
             P_NUM,
             CREATE_DATE)
          VALUES
            (V_OFR_ID,
             V_OFFER_ID,
             V_OFFER_TYPE,
             V_ACCT_LEVEL,
             V_RETURN_TYPE,
             V_OBJECT_LEVEL,
             V_SHOULD_SUBSIDY,
             V_MONTH_SUBSIDY,
             2,
             0,
             V_SUBSIDY_OBJECT,
             V_AS_OWN_FEE,
             V_CAN_DRAW,
             V_CAN_CARRY,
             -V_FIRST_MONTH_SUBSIDY,
             V_RETURN_RULE,
             1,
             SYSDATE);
        END IF;
      END IF;

    END LOOP;
    COMMIT;

    UPDATE BATCH_LOG@ICS
       SET END_TIME = SYSDATE, STATUS = 1
     WHERE SEQ = V_BATCH_LOG_INTERVAL;
    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      ERRCODE := SQLCODE;
      ERRMSG  := ERRCODE || ' : ' || SUBSTR(SQLERRM, 1, 200);

      UPDATE BATCH_LOG@ICS
         SET END_TIME = SYSDATE, STATUS = 0
       WHERE SEQ = V_BATCH_LOG_INTERVAL;

      EXECUTE IMMEDIATE '
   select PRINT_ERR_SEQ.NEXTVAL@ics  from dual'
        INTO V_BATCH_LOG_INTERVAL;

      INSERT INTO BATCH_ERR@ICS
        (SEQ, DESCRIPTION, ERROR_DESC, PROCESS_DATE)
      VALUES
        (V_BATCH_LOG_INTERVAL,
         'Error in JT_HB_COUNT.JT_HB_OFFER_LIST' || v_ofr_id,
         ERRMSG,
         SYSDATE);
      COMMIT;

  END JT_HB_OFFER_LIST;


  PROCEDURE JT_HB_OFFER_YINGBU IS
  ERRCODE              NUMBER;
  ERRMSG               VARCHAR2(200);
  ACT_MSG              NUMBER(2);
  TABLE_NAME           VARCHAR2(30);
  V_SQL                VARCHAR2(1000);
  V_BATCH_LOG_INTERVAL NUMBER;
  V_START_TIME         DATE;

  V_OFR_ID         NUMBER(14);
  V_OFFER_ID       VARCHAR2(30);
  V_SHOULD_SUBSIDY NUMBER(8);
  V_MONTH_SUBSIDY  NUMBER(8);
  --开游标
  CURSOR OFR_CURSOR IS
  SELECT OFR_ID, OFFER_ID, SHOULD_SUBSIDY, MONTH_SUBSIDY
  FROM JT_HB_OFFER_DEF
  --where rownum<10
  ;

  BEGIN
    --记日志表
    EXECUTE IMMEDIATE '
   select print_log_seq.NEXTVAL@ics  from dual'
    INTO V_BATCH_LOG_INTERVAL;

    V_START_TIME := SYSDATE;

    INSERT INTO BATCH_LOG@ICS
    (SEQ, START_TIME, STATUS, IN_MONTH, PROGRAM_NAME)
    VALUES
      (V_BATCH_LOG_INTERVAL,
       V_START_TIME,
       0,
       TO_CHAR(V_START_TIME, 'yyyymm'),
       'JT_HB_COUNT.JT_HB_OFFER_YINGBU');
    COMMIT;

    --清表
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_Yb';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_YB_1';

    OPEN OFR_CURSOR;
    LOOP

    FETCH OFR_CURSOR
    INTO V_OFR_ID, V_OFFER_ID, V_SHOULD_SUBSIDY, V_MONTH_SUBSIDY;
    EXIT WHEN OFR_CURSOR%NOTFOUND;

  INSERT INTO JT_HB_OFFER_YB_1
  (OFFER_ID, OFR_INST_CD, EFF_DATE, EXP_DATE)
    SELECT V_OFFER_ID, c.ofr_inst_cd, MIN(B.EFF_DATE), MAX(B.EXP_DATE)
    FROM hss.tb_prd_ofr_detail_inst_21@hss b,
      hss.tb_prd_ofr_inst_proc_21@hss   c
    WHERE B.ofr_id = V_ofr_id
          AND b.ofr_inst_id = c.ofr_inst_id
    GROUP BY c.ofr_inst_cd;
  COMMIT;

  END LOOP;

  INSERT INTO JT_HB_OFFER_YB
  (MONTH_ID,
   SUBSIDY_MODE,
   PRODUCT_OFFER_INSTANCE_ID,
   OFFER_ID,
   TOTAL_BALANCE,
   TOTAL_SUBSIDY,
   MONTH_SUBSIDY,
   LAST_MONTH_DEFERRED_SUBSIDY,
   MONTH_INVALID_SUBSIDY,
   MONTH_DEFERRED_SUBSIDY,
   PROV,
   CITY)


    SELECT to_char(add_months(SYSDATE, -1), 'yyyymm'),
      2,
      a.ofr_inst_cd,
      a.OFFER_ID,
      b.SHOULD_SUBSIDY -
      b.MONTH_SUBSIDY *
      months_between(TRUNC(add_months(SYSDATE, -1), 'MM'),
                     trunc(a.EFF_DATE, 'mm')),
      b.SHOULD_SUBSIDY,
      b.MONTH_SUBSIDY,
      0,
      0,
      0,
      '021',
      '021'
    FROM JT_HB_OFFER_YB_1 a, JT_HB_OFFER_def b
    where a.offer_id = b.offer_id
          and a.EFF_DATE <= TRUNC(add_months(SYSDATE, -1), 'MM')
          AND a.EXP_DATE > TRUNC(add_months(SYSDATE, -1), 'MM');

  UPDATE JT_HB_OFFER_YB a
  SET month_invalid_subsidy = month_subsidy -
                              (SELECT nvl(SUM(month_face_subsidy), 0)
                               FROM JT_HB_OFFER_shb b
                               WHERE b.product_offer_instance_id =
                                     a.product_offer_instance_id)
  WHERE 1 = 1;

  COMMIT;

  --稽核

  V_SQL:=' delete from JT_HB_OFFER_shb where product_offer_instance_id in (
   select product_offer_instance_id  from JT_HB_OFFER_yb where total_balance<0)';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  V_SQL:=' delete from JT_HB_OFFER_shb_ics where offer_id in (
   select product_offer_instance_id  from JT_HB_OFFER_yb where total_balance<0)';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  V_SQL:=' delete from JT_HB_OFFER_yb where product_offer_instance_id in (
   select product_offer_instance_id  from JT_HB_OFFER_yb where total_balance<0)';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;
  --稽核结束

  --备份历史表
  V_SQL := '
    INSERT INTO JT_HB_OFFER_YB_bak
      (MONTH_ID,
       SUBSIDY_MODE,
       PRODUCT_OFFER_INSTANCE_ID,
       OFFER_ID,
       TOTAL_BALANCE,
       TOTAL_SUBSIDY,
       MONTH_SUBSIDY,
       LAST_MONTH_DEFERRED_SUBSIDY,
       MONTH_INVALID_SUBSIDY,
       MONTH_DEFERRED_SUBSIDY,
       PROV,
       CITY,
       RESERVE_1,
       RESERVE_2,
       create_date)
      SELECT MONTH_ID,
             SUBSIDY_MODE,
             PRODUCT_OFFER_INSTANCE_ID,
             OFFER_ID,
             TOTAL_BALANCE,
             TOTAL_SUBSIDY,
             MONTH_SUBSIDY,
             LAST_MONTH_DEFERRED_SUBSIDY,
             MONTH_INVALID_SUBSIDY,
             MONTH_DEFERRED_SUBSIDY,
             PROV,
             CITY,
             RESERVE_1,
             RESERVE_2,
             SYSDATE
        FROM JT_HB_OFFER_YB';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  V_SQL := 'INSERT INTO  JT_HB_OFFER_shb_bak (
  month_id,
  subsidy_mode ,
  balance_acct_id ,
  product_offer_instance_id,
  offer_id,
  acct_id,
  serv_id ,
  month_face_subsidy,
  prov,
  city,
  reserve_1,
  reserve_2,
  create_date
  )
  select   month_id,
  subsidy_mode ,
  balance_acct_id ,
  product_offer_instance_id,
  offer_id,
  acct_id,
  serv_id ,
  month_face_subsidy,
  prov,
  city,
  reserve_1,
  reserve_2,
  sysdate from JT_HB_OFFER_shb';
  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  UPDATE BATCH_LOG@ICS
  SET END_TIME = SYSDATE, STATUS = 1
  WHERE SEQ = V_BATCH_LOG_INTERVAL;
  COMMIT;

  --刷剩余金额

  EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK;
  ERRCODE := SQLCODE;
  ERRMSG  := ERRCODE || ' : ' || SUBSTR(SQLERRM, 1, 200);

  UPDATE BATCH_LOG@ICS
  SET END_TIME = SYSDATE, STATUS = 0
  WHERE SEQ = V_BATCH_LOG_INTERVAL;

  EXECUTE IMMEDIATE '
   select PRINT_ERR_SEQ.NEXTVAL@ics  from dual'
  INTO V_BATCH_LOG_INTERVAL;

  INSERT INTO BATCH_ERR@ICS
  (SEQ, DESCRIPTION, ERROR_DESC, PROCESS_DATE)
  VALUES
    (V_BATCH_LOG_INTERVAL,
     'Error in JT_HB_COUNT.JT_HB_OFFER_YINGBU',
     ERRMSG,
     SYSDATE);
  COMMIT;

  END JT_HB_OFFER_YINGBU;


  PROCEDURE JT_HB_OFFER_SHIBU IS
  ERRCODE              NUMBER;
  ERRMSG               VARCHAR2(200);
  ACT_MSG              NUMBER(2);
  TABLE_NAME           VARCHAR2(30);
  V_SQL                VARCHAR2(1000);
  V_STATEMENT_DATE     VARCHAR2(6);
  V_BILL_REF_NO        VARCHAR2(12);
  V_PART               VARCHAR2(100);
  V_BATCH_LOG_INTERVAL NUMBER;
  V_BATCH_LOG_INTERVAL_1 NUMBER;
  V_START_TIME         DATE;
  v_count              NUMBER;
  v_serv_nbr           VARCHAR2(300);
  v_row_id           VARCHAR2(300);

  /*    CURSOR V_CUR IS
        SELECT distinct source_inst_id
          FROM JT_HB_OFFER_shb_1
         WHERE 1=1;*/
  CURSOR SERV_NO_CURSOR IS
  SELECT distinct service_nbr
  FROM JT_HB_OFFER_shb_2;

  BEGIN
    EXECUTE IMMEDIATE '
   select print_log_seq.NEXTVAL@ics  from dual'
    INTO V_BATCH_LOG_INTERVAL;

    V_START_TIME := SYSDATE;

    INSERT INTO BATCH_LOG@ICS
    (SEQ, START_TIME, STATUS, IN_MONTH, PROGRAM_NAME)
    VALUES
      (V_BATCH_LOG_INTERVAL,
       V_START_TIME,
       0,
       TO_CHAR(V_START_TIME, 'yyyymm'),
       'JT_HB_COUNT.JT_HB_OFFER_SHIBU ');
    COMMIT;

    V_STATEMENT_DATE := TO_CHAR(SYSDATE, 'yyyymm');

    --清表
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_1';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_2';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_3';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_4';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_5';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_ics';
    -- EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_bak';

    --取abo的帐单明细中的销售策略
    V_SQL := ' INSERT INTO JT_HB_OFFER_shb_1 (offer_id,pay_acct_id,prd_inst_id,acct_item_type_id,charge,source_inst_id)
  SELECT a.offer_id,b.pay_acct_id,prd_inst_id,acct_item_type_id,charge,source_inst_id
    FROM jt_hb_offer_def a,dbusr06.bill_t_detail_abp b
    WHERE a.ofr_id =b.ofr_id
     and a.month_subsidy>0
    --测试用
    -- and p_num=2
    --and a.offer_id=''2-CM6IBL0''
     ';
    EXECUTE IMMEDIATE V_SQL;
    COMMIT;




    v_count:=0;
    for rec in 0..9
    loop
    V_SQL := 'insert into JT_HB_OFFER_shb_4(offer_id,pay_acct_id,prd_inst_id,acct_item_type_id,source_inst_id,ofr_inst_cd,charge)
SELECT a.offer_id,a.pay_acct_id,a.prd_inst_id,a.acct_item_type_id,a.source_inst_id,b.ofr_inst_cd,charge
 FROM  JT_HB_OFFER_shb_1 a
,hss.tb_prd_ofr_inst_proc_21@hss b
WHERE a.source_inst_id=b.ofr_inst_id
--and sysdate>b.eff_date
--and sysdate<b.exp_date
and b.eff_date<=trunc(add_months(sysdate,-1),''MM'')
and b.exp_date>trunc(add_months(sysdate,-1),''MM'')
and mod(a.source_inst_id,10)='||v_count;

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;
  v_count:=v_count+1;

  end loop;

  V_SQL := 'INSERT INTO JT_HB_OFFER_shb_3 (offer_id,pay_acct_id,prd_inst_id,acct_item_type_id,source_inst_id,ofr_inst_cd,charge)
SELECT a.offer_id,a.pay_acct_id,a.prd_inst_id,a.acct_item_type_id,a.source_inst_id,a.ofr_inst_cd,sum(charge)
FROM  JT_HB_OFFER_shb_4 a
GROUP BY a.pay_acct_id,a.prd_inst_id,a.acct_item_type_id,a.offer_id,a.source_inst_id,a.ofr_inst_cd';


  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  --V_PART := 'P_BI_201406';
  V_PART := 'P_BI_' || to_char(SYSDATE, 'yyyymm');


  V_SQL := ' INSERT INTO  JT_HB_OFFER_shb_2 (PAY_ACCT_ID,
                       PRD_INST_ID,
                       ACCT_ITEM_TYPE_ID,
                       OFFER_ID,
                       SOURCE_INST_ID,
                       OFR_INST_CD,
                       CHARGE,
                       SERVICE_NBR,
                       BILL_REF_NO,
                       PRD_INST_CODE
)
   SELECT a.pay_acct_id,a.prd_inst_id,a.acct_item_type_id,a.offer_id,a.source_inst_id,a.ofr_inst_cd,charge,c.service_nbr,b.bill_ref_no,c.PRD_INST_CODE
    FROM JT_HB_OFFER_shb_3 a,bill_invoice PARTITION (' ||
  V_PART ||
  ') b,dbusr06.BILL_T_PRD_INST_ABP c
WHERE a.pay_acct_id =b.account_no
and a.prd_inst_id=c.prd_inst_id
and statement_date=trunc(sysdate,''MM'')
and b.status<>-3
and b.interim_bill_flag=0
and c.eff_date<=trunc(add_months(sysdate,-1),''MM'')
and c.exp_date>trunc(add_months(sysdate,-1),''MM'')';
  --group by a.pay_acct_id,a.prd_inst_id,a.acct_item_type_id,a.offer_id,a.source_inst_id,a.ofr_inst_cd,charge,c.service_nbr';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  v_count:=0;
  --匹配crm的row_id
  IF SERV_NO_CURSOR%ISOPEN THEN
    CLOSE SERV_NO_CURSOR;
  END IF;

  OPEN SERV_NO_CURSOR;
  LOOP


  FETCH SERV_NO_CURSOR
  INTO v_serv_nbr;
  EXIT WHEN SERV_NO_CURSOR%NOTFOUND;

  --DBMS_OUTPUT.PUT_LINE(v_serv_nbr);
  begin
    select nvl(max(b.row_id),'0') into v_row_id from Siebel.s_asset@crmquery b
    where b.serial_num= v_serv_nbr
          And status_cd='活动'
          And par_asset_id is null
          and rownum=1;

    if v_row_id='0' then
      select nvl(max(b.row_id),'0') into v_row_id from Siebel.s_asset@crmquery b
      where b.serial_num= v_serv_nbr
            and rownum=1;
    end if;

    v_sql:='insert into JT_HB_OFFER_shb_5(service_nbr,row_id)
     values(:1,:2)';
    EXECUTE IMMEDIATE V_SQL using v_serv_nbr,v_row_id;

    EXCEPTION
  WHEN OTHERS THEN
  --ROLLBACK;
  ERRCODE := SQLCODE;
  ERRMSG  := ERRCODE || ' : ' || SUBSTR(SQLERRM, 1, 200);

  EXECUTE IMMEDIATE '
   select PRINT_ERR_SEQ.NEXTVAL@ics  from dual'
  INTO V_BATCH_LOG_INTERVAL_1;

  INSERT INTO BATCH_ERR@ICS
  (SEQ, DESCRIPTION, ERROR_DESC, PROCESS_DATE)
  VALUES
    (V_BATCH_LOG_INTERVAL_1,
     'Error in JT_HB_COUNT.JT_HB_OFFER_SHIBU',
     ERRMSG,
     SYSDATE);
  COMMIT;

  end;

  IF v_count>1000
  THEN
    v_count :=0;
    COMMIT;
  ELSE
    v_count :=v_count +1;
  END IF;

  end LOOP;
  --提供集团的实补数据

  commit;


  V_SQL := ' INSERT INTO  JT_HB_OFFER_shb (MONTH_ID,
                       SUBSIDY_MODE,
                       BALANCE_ACCT_ID,
                       PRODUCT_OFFER_INSTANCE_ID,
                       OFFER_ID,
                       ACCT_ID,
                       SERV_ID,
                       MONTH_FACE_SUBSIDY,
                       PROV,
                       CITY
                       )
   SELECT to_char(add_months(SYSDATE,-1),''yyyymm''),2,'''',a.ofr_inst_cd,a.offer_id,a.pay_acct_id,b.row_id,-sum(a.charge),''021'',''021''
    FROM JT_HB_OFFER_shb_2  a,JT_HB_OFFER_shb_5 b
    where a.service_nbr=b.service_nbr
     group by a.pay_acct_id,a.offer_id,a.source_inst_id,a.ofr_inst_cd,b.row_id';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  --稽核--删除实补金额为负的记录
  V_SQL := ' DELETE FROM JT_HB_OFFER_shb_2 WHERE ofr_inst_cd IN (
    SELECT PRODUCT_OFFER_INSTANCE_ID FROM JT_HB_OFFER_shb WHERE MONTH_FACE_SUBSIDY<0)';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  V_SQL := ' DELETE FROM JT_HB_OFFER_shb WHERE PRODUCT_OFFER_INSTANCE_ID IN (
    SELECT PRODUCT_OFFER_INSTANCE_ID FROM JT_HB_OFFER_shb WHERE MONTH_FACE_SUBSIDY<0)';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  --稽核--删除实补金额大于应补金额的记录

  V_SQL := 'delete from JT_HB_OFFER_shb_2 where ofr_inst_cd  in (
    select product_offer_instance_id from (select product_offer_instance_id,offer_id,sum(month_face_subsidy) charge
      from JT_HB_OFFER_shb group by product_offer_instance_id,offer_id) a, JT_HB_OFFER_DEF b
      where a.offer_id=b.offer_id and a.charge>b.month_subsidy)';


  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  V_SQL := 'delete from JT_HB_OFFER_shb where product_offer_instance_id in (
    select product_offer_instance_id from (select product_offer_instance_id,offer_id,sum(month_face_subsidy) charge
      from JT_HB_OFFER_shb group by product_offer_instance_id,offer_id) a, JT_HB_OFFER_DEF b
      where a.offer_id=b.offer_id and a.charge>b.month_subsidy)';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  --提供给ics的实补数据

  /*   V_SQL := 'INSERT INTO  JT_HB_OFFER_shb_ics (
       bill_ref_no,account_no,subscr_no,user_id,sub_type_code,offer_id,yingbu_amount,shibu_amount)
   SELECT  bill_ref_no,pay_acct_id,prd_inst_id,service_nbr,acct_item_type_id,offer_id,sum(charge) yingbu_amount,sum(charge) shibu_amount
           FROM  JT_HB_OFFER_shb_2
  group by bill_ref_no,pay_acct_id,prd_inst_id,service_nbr,acct_item_type_id,offer_id';

      EXECUTE IMMEDIATE V_SQL;
      COMMIT;*/

  V_SQL := 'INSERT INTO  JT_HB_OFFER_shb_ics (
     bill_ref_no,account_no,subscr_no,user_id,sub_type_code,offer_id,yingbu_amount,shibu_amount)
 SELECT  bill_ref_no,pay_acct_id,prd_inst_id,service_nbr,acct_item_type_id,ofr_inst_cd,-sum(charge) yingbu_amount,-sum(charge) shibu_amount
         FROM  JT_HB_OFFER_shb_2
group by bill_ref_no,pay_acct_id,prd_inst_id,service_nbr,acct_item_type_id,ofr_inst_cd';



  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  V_SQL := 'delete from ods_hb_offer_shb@ics where 1=1';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;


  V_SQL := 'insert into ods_hb_offer_shb@ics
  (BILL_REF_NO,
   ACCOUNT_NO,
   SUBSCR_NO,
   USER_ID,
   SUB_TYPE_CODE,
   OFFER_ID,
   YINGBU_AMOUNT,
   SHIBU_AMOUNT,
   CREATE_DATE)
  select bill_ref_no,
         account_no,
         subscr_no,
         user_id,
         sub_type_code,
         offer_id,
         yingbu_amount,
         shibu_amount,
         sysdate create_date
    from JT_HB_OFFER_shb_ics a';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;
  --稽核
  /*    V_SQL := ' DELETE FROM JT_HB_OFFER_shb_ics WHERE ofr_inst_cd IN (
      SELECT ofr_inst_cd FROM JT_HB_OFFER_shb WHERE shibu_amount<0)';

      COMMIT;
      EXECUTE IMMEDIATE V_SQL;*/
  --稽核

  --备份



  UPDATE BATCH_LOG@ICS
  SET END_TIME = SYSDATE, STATUS = 1
  WHERE SEQ = V_BATCH_LOG_INTERVAL;
  COMMIT;

  EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK;
  ERRCODE := SQLCODE;
  ERRMSG  := ERRCODE || ' : ' || SUBSTR(SQLERRM, 1, 200);

  UPDATE BATCH_LOG@ICS
  SET END_TIME = SYSDATE, STATUS = 0
  WHERE SEQ = V_BATCH_LOG_INTERVAL;

  EXECUTE IMMEDIATE '
   select PRINT_ERR_SEQ.NEXTVAL@ics  from dual'
  INTO V_BATCH_LOG_INTERVAL_1;

  INSERT INTO BATCH_ERR@ICS
  (SEQ, DESCRIPTION, ERROR_DESC, PROCESS_DATE)
  VALUES
    (V_BATCH_LOG_INTERVAL_1,
     'Error in JT_HB_COUNT.JT_HB_OFFER_SHIBU',
     ERRMSG,
     SYSDATE);
  COMMIT;

  END JT_HB_OFFER_SHIBU;



  PROCEDURE JT_HB_OFFER_SHIBU_OCS IS
  ERRCODE              NUMBER;
  ERRMSG               VARCHAR2(200);
  ACT_MSG              NUMBER(2);
  TABLE_NAME           VARCHAR2(30);
  V_SQL                VARCHAR2(1000);
  V_STATEMENT_DATE     VARCHAR2(6);
  V_BILL_REF_NO        VARCHAR2(12);
  V_PART               VARCHAR2(100);
  V_BATCH_LOG_INTERVAL NUMBER;
  V_BATCH_LOG_INTERVAL_1 NUMBER;
  V_START_TIME         DATE;
  v_count              NUMBER;
  v_serv_nbr           VARCHAR2(300);
  v_row_id           VARCHAR2(300);

  /*    CURSOR V_CUR IS
        SELECT distinct source_inst_id
          FROM JT_HB_OFFER_shb_1
         WHERE 1=1;*/
  CURSOR SERV_NO_CURSOR IS
  SELECT distinct service_nbr
  FROM JT_HB_OFFER_shb_2;

  BEGIN
    EXECUTE IMMEDIATE '
   select print_log_seq.NEXTVAL@ics  from dual'
    INTO V_BATCH_LOG_INTERVAL;

    V_START_TIME := SYSDATE;

    INSERT INTO BATCH_LOG@ICS
    (SEQ, START_TIME, STATUS, IN_MONTH, PROGRAM_NAME)
    VALUES
      (V_BATCH_LOG_INTERVAL,
       V_START_TIME,
       0,
       TO_CHAR(V_START_TIME, 'yyyymm'),
       'JT_HB_COUNT.JT_HB_OFFER_SHIBU_OCS ');
    COMMIT;

    V_STATEMENT_DATE := TO_CHAR(SYSDATE, 'yyyymm');

    --清表
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_1';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_2';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_3';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_4';
    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_5';
    --    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb';
    --    EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_ics';
    -- EXECUTE IMMEDIATE 'truncate table JT_HB_OFFER_shb_bak';

    --取ocs的帐单明细中的销售策略
    V_SQL := ' INSERT INTO JT_HB_OFFER_shb_1 (offer_id,pay_acct_id,prd_inst_id,acct_item_type_id,charge,source_inst_id)
  SELECT a.offer_id,b.pay_acct_id,prd_inst_id,acct_item_type_id,charge,source_inst_id
    FROM jt_hb_offer_def a,dbusr06.DAT_M_21_OCS b
    WHERE a.ofr_id =b.ofr_id
     and a.month_subsidy>0
    --测试用
    -- and p_num=2
    --and a.offer_id=''2-CM6IBL0''
     ';
    EXECUTE IMMEDIATE V_SQL;
    COMMIT;




    v_count:=0;
    for rec in 0..9
    loop
    V_SQL := 'insert into JT_HB_OFFER_shb_4(offer_id,pay_acct_id,prd_inst_id,acct_item_type_id,source_inst_id,ofr_inst_cd,charge)
SELECT a.offer_id,a.pay_acct_id,a.prd_inst_id,a.acct_item_type_id,a.source_inst_id,b.ofr_inst_cd,charge
 FROM  JT_HB_OFFER_shb_1 a
,hss.tb_prd_ofr_inst_proc_21@hss b
WHERE a.source_inst_id=b.ofr_inst_id
--and sysdate>b.eff_date
--and sysdate<b.exp_date
and b.eff_date<=trunc(add_months(sysdate,-1),''MM'')
and b.exp_date>trunc(add_months(sysdate,-1),''MM'')
and mod(a.source_inst_id,10)='||v_count;

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;
  v_count:=v_count+1;

  end loop;

  V_SQL := 'INSERT INTO JT_HB_OFFER_shb_3 (offer_id,pay_acct_id,prd_inst_id,acct_item_type_id,source_inst_id,ofr_inst_cd,charge)
SELECT a.offer_id,a.pay_acct_id,a.prd_inst_id,a.acct_item_type_id,a.source_inst_id,a.ofr_inst_cd,sum(charge)
FROM  JT_HB_OFFER_shb_4 a
GROUP BY a.pay_acct_id,a.prd_inst_id,a.acct_item_type_id,a.offer_id,a.source_inst_id,a.ofr_inst_cd';


  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  --V_PART := 'P_O_BI_201406';
  V_PART := 'P_O_BI_' || to_char(SYSDATE, 'yyyymm');


  V_SQL := ' INSERT INTO  JT_HB_OFFER_shb_2 (PAY_ACCT_ID,
                       PRD_INST_ID,
                       ACCT_ITEM_TYPE_ID,
                       OFFER_ID,
                       SOURCE_INST_ID,
                       OFR_INST_CD,
                       CHARGE,
                       SERVICE_NBR,
                       BILL_REF_NO,
                       PRD_INST_CODE
)
   SELECT a.pay_acct_id,a.prd_inst_id,a.acct_item_type_id,a.offer_id,a.source_inst_id,a.ofr_inst_cd,charge,c.service_nbr,b.bill_ref_no,c.PRD_INST_CODE
    FROM JT_HB_OFFER_shb_3 a,iam.ocs_bill_invoice PARTITION (' ||
  V_PART ||
  ') b,dbusr06.BILL_T_PRD_INST_ABP c
WHERE a.pay_acct_id =b.account_no
and a.prd_inst_id=c.prd_inst_id
and statement_date=trunc(sysdate,''MM'')
and b.status<>-3
and b.interim_bill_flag=0
and c.eff_date<=trunc(add_months(sysdate,-1),''MM'')
and c.exp_date>trunc(add_months(sysdate,-1),''MM'')';
  --group by a.pay_acct_id,a.prd_inst_id,a.acct_item_type_id,a.offer_id,a.source_inst_id,a.ofr_inst_cd,charge,c.service_nbr';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  v_count:=0;
  --匹配crm的row_id
  IF SERV_NO_CURSOR%ISOPEN THEN
    CLOSE SERV_NO_CURSOR;
  END IF;

  OPEN SERV_NO_CURSOR;
  LOOP


  FETCH SERV_NO_CURSOR
  INTO v_serv_nbr;
  EXIT WHEN SERV_NO_CURSOR%NOTFOUND;

  --DBMS_OUTPUT.PUT_LINE(v_serv_nbr);
  begin
    select nvl(max(b.row_id),'0') into v_row_id from Siebel.s_asset@crmquery b
    where b.serial_num= v_serv_nbr
          And status_cd='活动'
          And par_asset_id is null
          and rownum=1;

    if v_row_id='0' then
      select nvl(max(b.row_id),'0') into v_row_id from Siebel.s_asset@crmquery b
      where b.serial_num= v_serv_nbr
            and rownum=1;
    end if;

    v_sql:='insert into JT_HB_OFFER_shb_5(service_nbr,row_id)
     values(:1,:2)';
    EXECUTE IMMEDIATE V_SQL using v_serv_nbr,v_row_id;

    EXCEPTION
  WHEN OTHERS THEN
  --ROLLBACK;
  ERRCODE := SQLCODE;
  ERRMSG  := ERRCODE || ' : ' || SUBSTR(SQLERRM, 1, 200);

  EXECUTE IMMEDIATE '
   select PRINT_ERR_SEQ.NEXTVAL@ics  from dual'
  INTO V_BATCH_LOG_INTERVAL_1;

  INSERT INTO BATCH_ERR@ICS
  (SEQ, DESCRIPTION, ERROR_DESC, PROCESS_DATE)
  VALUES
    (V_BATCH_LOG_INTERVAL_1,
     'Error in JT_HB_COUNT.JT_HB_OFFER_SHIBU_OCS',
     ERRMSG,
     SYSDATE);
  COMMIT;

  end;

  IF v_count>1000
  THEN
    v_count :=0;
    COMMIT;
  ELSE
    v_count :=v_count +1;
  END IF;

  end LOOP;
  --提供集团的实补数据

  commit;


  V_SQL := ' INSERT INTO  JT_HB_OFFER_shb (MONTH_ID,
                       SUBSIDY_MODE,
                       BALANCE_ACCT_ID,
                       PRODUCT_OFFER_INSTANCE_ID,
                       OFFER_ID,
                       ACCT_ID,
                       SERV_ID,
                       MONTH_FACE_SUBSIDY,
                       PROV,
                       CITY
                       )
   SELECT to_char(add_months(SYSDATE,-1),''yyyymm''),2,'''',a.ofr_inst_cd,a.offer_id,a.pay_acct_id,b.row_id,-sum(a.charge),''021'',''021''
    FROM JT_HB_OFFER_shb_2  a,JT_HB_OFFER_shb_5 b
    where a.service_nbr=b.service_nbr
     group by a.pay_acct_id,a.offer_id,a.source_inst_id,a.ofr_inst_cd,b.row_id';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  --稽核--删除实补金额为负的记录
  V_SQL := ' DELETE FROM JT_HB_OFFER_shb_2 WHERE ofr_inst_cd IN (
    SELECT PRODUCT_OFFER_INSTANCE_ID FROM JT_HB_OFFER_shb WHERE MONTH_FACE_SUBSIDY<0)';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  V_SQL := ' DELETE FROM JT_HB_OFFER_shb WHERE PRODUCT_OFFER_INSTANCE_ID IN (
    SELECT PRODUCT_OFFER_INSTANCE_ID FROM JT_HB_OFFER_shb WHERE MONTH_FACE_SUBSIDY<0)';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  --稽核--删除实补金额大于应补金额的记录

  V_SQL := 'delete from JT_HB_OFFER_shb_2 where ofr_inst_cd  in (
    select product_offer_instance_id from (select product_offer_instance_id,offer_id,sum(month_face_subsidy) charge
      from JT_HB_OFFER_shb group by product_offer_instance_id,offer_id) a, JT_HB_OFFER_DEF b
      where a.offer_id=b.offer_id and a.charge>b.month_subsidy)';


  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  V_SQL := 'delete from JT_HB_OFFER_shb where product_offer_instance_id in (
    select product_offer_instance_id from (select product_offer_instance_id,offer_id,sum(month_face_subsidy) charge
      from JT_HB_OFFER_shb group by product_offer_instance_id,offer_id) a, JT_HB_OFFER_DEF b
      where a.offer_id=b.offer_id and a.charge>b.month_subsidy)';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  --提供给ics的实补数据


  V_SQL := 'INSERT INTO  JT_HB_OFFER_shb_ics (
     bill_ref_no,account_no,subscr_no,user_id,sub_type_code,offer_id,yingbu_amount,shibu_amount)
 SELECT  bill_ref_no,pay_acct_id,prd_inst_id,service_nbr,acct_item_type_id,ofr_inst_cd,-sum(charge) yingbu_amount,-sum(charge) shibu_amount
         FROM  JT_HB_OFFER_shb_2
group by bill_ref_no,pay_acct_id,prd_inst_id,service_nbr,acct_item_type_id,ofr_inst_cd';


  EXECUTE IMMEDIATE V_SQL;
  COMMIT;

  V_SQL := 'delete from ods_hb_offer_shb@ics where 1=1';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;


  V_SQL := 'insert into ods_hb_offer_shb@ics
  (BILL_REF_NO,
   ACCOUNT_NO,
   SUBSCR_NO,
   USER_ID,
   SUB_TYPE_CODE,
   OFFER_ID,
   YINGBU_AMOUNT,
   SHIBU_AMOUNT,
   CREATE_DATE)
  select bill_ref_no,
         account_no,
         subscr_no,
         user_id,
         sub_type_code,
         offer_id,
         yingbu_amount,
         shibu_amount,
         sysdate create_date
    from JT_HB_OFFER_shb_ics a';

  EXECUTE IMMEDIATE V_SQL;
  COMMIT;
  --稽核
  /*    V_SQL := ' DELETE FROM JT_HB_OFFER_shb_ics WHERE ofr_inst_cd IN (
      SELECT ofr_inst_cd FROM JT_HB_OFFER_shb WHERE shibu_amount<0)';

      COMMIT;
      EXECUTE IMMEDIATE V_SQL;*/
  --稽核

  --备份



  UPDATE BATCH_LOG@ICS
  SET END_TIME = SYSDATE, STATUS = 1
  WHERE SEQ = V_BATCH_LOG_INTERVAL;
  COMMIT;

  EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK;
  ERRCODE := SQLCODE;
  ERRMSG  := ERRCODE || ' : ' || SUBSTR(SQLERRM, 1, 200);

  UPDATE BATCH_LOG@ICS
  SET END_TIME = SYSDATE, STATUS = 0
  WHERE SEQ = V_BATCH_LOG_INTERVAL;

  EXECUTE IMMEDIATE '
   select PRINT_ERR_SEQ.NEXTVAL@ics  from dual'
  INTO V_BATCH_LOG_INTERVAL_1;

  INSERT INTO BATCH_ERR@ICS
  (SEQ, DESCRIPTION, ERROR_DESC, PROCESS_DATE)
  VALUES
    (V_BATCH_LOG_INTERVAL_1,
     'Error in JT_HB_COUNT.JT_HB_OFFER_SHIBU_OCS',
     ERRMSG,
     SYSDATE);
  COMMIT;

  END JT_HB_OFFER_SHIBU_OCS;